<?php
class QueryExecuteTest
{
    protected function getQuery () {
        return DatabaseFactory::create('MySQL')
            ->connect    ('localhost', 'Shock', '')
            ->setDbName  ('php')
            ->setCharset ('utf8')
            ->setPrefix  ('DB')
            ->query();
    }

    public function select () {
        $id = 20;
        $query = $this->getQuery()->select()
        ->fields('`Post`.*', '`Author`.*', 'COUNT( `Post`.`ID` ) AS `Posts`')
        ->table("Posts", "Post")
        ->innerJoin("Authors", "Author", "`Author`.`ID` = `Post`.`AuthorID`")
        ->innerJoin("Posts", "PostCount", "`Author`.`ID` = `PostCount`.`AuthorID`")
        ->where("`Post`.`Deleted` = %d", 0)
        ->where("`PostCount`.`Deleted` = %d", 0)
        ->group("`Post`.`ID`")
        ->limit(0, 30)
        ->order('`Post`.`Date`', 1) // DESC
        ->order('`Post`.`ID`') // ASC
        ->compile()->run();
        $i = 0;
        while($query->row()) {
            echo "\n\n" . ++$i . ".";

            echo "\n"; // поле `Text` таблицы `Post`
            print_r( $query->get('Post.Text') );

            echo "\n"; // Все поля таблицы `Author`
            print_r( $query->get('Author.*') );

            echo "\n"; // Все внетабличные поля (COUNT, SUM)
            print_r( $query->get('.*') );

            echo "\n"; // Внетабличное поле `Posts`
            print_r( $query->get('.Posts') );

            echo "\n"; // Двумерный ассоциативный массив — все данные
            print_r( $query->get('*.*') );

            echo "\n"; // Число-ассоциативный массив — аналог mysql_fetch_array
            print_r( $query->get('*') );
        }
    }

    public function update ($value = 1) {
        $id = 5;
        $query = $this->getQuery()
            ->update ()
            ->table ('Posts')
            ->set   ("`Deleted` = '%d'", $value)
            ->set   ("`Date` = `Date` + 1")
            ->where ("`ID` = '%d'", $id)
            ->compile()->run();
        echo $query->getNum();
    }

    public function update2 ($value = 1) {
        $query = $this->getQuery()
            ->update ()
            ->table  ('Posts', 'Post')
            ->innerJoin('Authors', 'Author', '`Author`.`ID` = `Post`.`AuthorID`')
            ->set    ("`Deleted` = '%d'", $value)
            ->where  ("`Author`.`Name` LIKE '%%%s%%'", 'hoc')
            ->orWhere("`Author`.`Name` LIKE '%%%s%%'", 'iki')
            ->compile()->run();
        echo $query->getNum();
    }

    public function insert () {
        $id = 5;
        $query = $this->getQuery()
            ->insert ()
            ->table  ('Posts')
            ->fields ('ID','Text','AuthorID','Date','Deleted')
            ->values (NULL , 'Tes"t7', '3', time(), '0')
            ->values (array(NULL , 'Test8', '2', time(), '0'))
            ->compile()->run();
        echo $query->getNum(), ' ', $query->getId();
    }

    public function delete () {
        $id = 4;
        $query = $this->getQuery()
            ->delete  ()
            ->table   ('Posts')
            ->where   ("`ID` > '%d'", $id)
            ->limit   (2)
            ->compile()->run();
        echo $query->getNum();
    }

}